package com.dsi.capa.db.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.sql.DataSource;
import javax.swing.JOptionPane;

import com.dsi.capa.db.model.Empleado;
import com.dsi.capa.db.model.Pago;

public class PagoDAO extends BasicDAO<Pago> {
	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;

	public PagoDAO() {
		super();
	}

	public PagoDAO(DataSource ds) {
		super(ds);
	}

	// insertar en la base
	@Override
	public void save(Pago target) {

		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO PAGO(IDPAGO,IDCOLECTOR, MONTOPAGO, FECHAPAGO, CARNET,DETALLEPAGO,TIPOPAGO)");
		sql.append(" VALUES(?, ?, ?, ?, ?, ?)");
		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());
			preparedStatement.setString(1, target.getIdPago());
			preparedStatement.setString(2, target.getIdColector());
			
			preparedStatement.setDouble(3, target.getMonto());
			preparedStatement.setString(4, target.getFecha());
			preparedStatement.setString(5, target.getCarnet());
			
//			preparedStatement.setString(1, "11");
//			preparedStatement.setString(2, "1");
//			preparedStatement.setInt(3, 9);
//			preparedStatement.setDouble(4,40);
//			preparedStatement.setString(5,"2013-04-04");
//			preparedStatement.setString(6, "ac05123");
			// JOptionPane.showMessageDialog(null, sql.toString());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();// JOptionPane.showMessageDialog(null,
								// e.getMessage());
		} finally {
			closeConnection();
		}
	}

	@Override
	public void update(Pago target) {
		StringBuffer sql = new StringBuffer();
		sql.append("UPDATE Pago SET carnet = ?, FECHAPAGO = ?, MONTOPAGO= ?");
		//sql.append("WHERE CARNET = ? AND FECHA = ?");
		sql.append("WHERE CARNET = ? ");
		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());

			preparedStatement.setString(1, target.getCarnet());
			preparedStatement.setString(2, "2013-01-01");
			preparedStatement.setDouble(3, target.getMonto());
			preparedStatement.setString(4, target.getCarnet());
			//preparedStatement.setDate(5, target.getFecha());
			
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	@Override
	public void delete(Pago target) {
		StringBuffer sql = new StringBuffer();
		sql.append("DELETE FROM PAGO WHERE CARNET = ? and CUOTA= ?");
		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());
			preparedStatement.setString(1, target.getCarnet());
			preparedStatement.setDouble(2, target.getCuota());
			// preparedStatement.setLong(1, target.getCodigo());
			preparedStatement.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}
	}

	@Override
	public List<Pago> findAll() {
		return findAll(null, null);
	}

	/************************* METODO QUE BUSCA UN SOLO PAGO *********************************/
	/*
	 * public Pago buscarPago(Pago target) { Pago c = new Pago(); StringBuffer
	 * sql = new StringBuffer();
	 * sql.append("SELECT carnet, fecha, idusuario, ");
	 * sql.append("descripcion, cuota, monto FROM Factura WHERE CARNET = ?");
	 * 
	 * try { preparedStatement =
	 * getConnection().prepareStatement(sql.toString());
	 * preparedStatement.setString(1, target.getCarnet()); resultSet =
	 * preparedStatement.executeQuery();
	 * 
	 * while(resultSet.next()) { c.setCarnet(resultSet.getString("CARNET"));
	 * c.setFecha(resultSet.getString("fecha"));
	 * c.setIdUsuario(resultSet.getString("idusuario"));
	 * c.setDescripcion(resultSet.getString("descripcion"));
	 * c.setCuota(resultSet.getInt("cuota"));
	 * c.setMonto(resultSet.getDouble("monto"));
	 * 
	 * }
	 * 
	 * 
	 * } catch (SQLException e) { e.printStackTrace(); } finally {
	 * closeConnection(); }
	 * 
	 * return c;
	 * 
	 * }//FIN METODO buscarPago
	 */

	public String obtenerMontoCuota(Pago target) {

		String result = null;

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * from carrera where =?");

		try {
			preparedStatement = getConnection()
					.prepareStatement(sql.toString());
			preparedStatement.setString(1, target.getDropdown());
			resultSet = preparedStatement.executeQuery();

			while (resultSet.next()) {
				result = resultSet.getString("MONTOCUOTA");

			}

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			closeConnection();
		}

		return result;
	}

	/************************* METODO QUE BUSCA UN SOLO PAGO *********************************/
	/*
	 * public String correlativo(Pago target) { //Pago c = new Pago(); String
	 * c=null; StringBuffer sql = new StringBuffer();
	 * sql.append("SELECT CARNET FROM Pago WHERE CARNET LIKE ?");
	 * 
	 * try { preparedStatement =
	 * getConnection().prepareStatement(sql.toString());
	 * preparedStatement.setString(1, target.getCarnet() + "%"); resultSet =
	 * preparedStatement.executeQuery();
	 * 
	 * if(resultSet.last()) { c = resultSet.getString("CARNET"); c =
	 * c.substring(4);//OBTENER ULTIMO CORRELATIVO //INCREMENTAR CORRELATIVO c =
	 * String.format("%03d", (Integer.parseInt(c))+1); } //return c;
	 * 
	 * } catch (SQLException e) { e.printStackTrace(); } finally {
	 * closeConnection(); }
	 * 
	 * return c;
	 * 
	 * }//FIN METODO buscarPago
	 */

	/** Bisquedas y navegacion **/

	public List<Pago> findBy(String idColector, String descripcion, Double cuota,String fechaInicial,String fechaFinal,Integer firstRow,
			Integer maxRows) {
		List<Pago> registros = new ArrayList<Pago>();
		StringBuffer sql = new StringBuffer();
		Connection connection = null;
		PreparedStatement preparedStatement = null;
		ResultSet resultSet = null;
		int i = 1;
		int pos = firstRow.intValue() + maxRows.intValue();
		sql.append("SELECT PAGO.IDPAGO ");
		sql.append("     , IDCOLECTOR ");
		sql.append("     , MONTOPAGO ");
		sql.append("     , CARNET ");
		sql.append("     , FECHAPAGO ");
		sql.append("     , DESCRIPCIONPAGO ");
		sql.append("     , CUOTA ");
		sql.append(" FROM PAGO ");
//		sql.append(" JOIN DETALLEPAGO ");
//		sql.append("   ON  PAGO.IDDETALLEP=DETALLEPAGO.IDDETALLEP");
		sql.append(" WHERE 1 = 1 ");
	
		if (!isEmpty(idColector)) {
			sql.append(" AND IDCOLECTOR LIKE ?");
		}
		if (!isEmpty(fechaInicial)&&!isEmpty(fechaFinal)) {
			sql.append(" AND (FECHAPAGO BETWEEN ? AND ?)");
		}

		try {
			connection = ds.getConnection();
			preparedStatement = connection.prepareStatement(sql.toString());
			if (!isEmpty(idColector)) {
				preparedStatement.setString(i++, idColector);
			}
			if (!isEmpty(fechaInicial)&&!isEmpty(fechaFinal)) {
//				preparedStatement.setDate(i++,  (java.sql.Date) fechaInicial);
//				preparedStatement.setDate(i++,  (java.sql.Date) fechaFinal);
				preparedStatement.setString(i++,fechaInicial);
				preparedStatement.setString(i++,fechaFinal);
				System.out.println(fechaInicial.toString());
			}

			resultSet = preparedStatement.executeQuery();
			i = 0;
			while (resultSet.next()) {
				if (i >= firstRow) {
					Pago cn = getDataRow(resultSet);
					registros.add(cn);
					if (i >= pos) {
						break;
					}
				}
				i++;
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return registros;
	}

	private Pago getDataRow(ResultSet resultSet) throws SQLException {
		Pago cn = new Pago();
		cn.setCarnet(resultSet.getString("CARNET"));
		cn.setFecha(resultSet.getDate("FECHAPAGO"));
		cn.setIdColector(resultSet.getString("IDCOLECTOR"));
		cn.setCuota(resultSet.getDouble("CUOTA"));
		cn.setDescripcion(resultSet.getString("DESCRIPCIONPAGO"));
		cn.setMonto(resultSet.getDouble("MONTOPAGO"));
		//cn.setCuota(resultSet.getDouble("CUOTA"));

		return cn;
	}

	private boolean isEmpty(Long target) {
		return target == null || target.longValue() == 0;
	}

	private boolean isEmpty(String target) {
		return target == null || target.trim().equals("");
	}

	private boolean isEmpty(Integer target) {
		return target == null || target.intValue() == 0;
	}

	private boolean isEmpty(Date target) {
		return target == null || target.toString().equals("");
	}

	@Override
	public List<Pago> findAll(Integer firstRow, Integer maxRows) {
		// TODO Auto-generated method stub
		return null;
	}

	/**** ----------------------------------- ***/

	
}
